Updating Data in a DataSet

When updating a row at the data source, you call the Update statement. The Update statement uses parameters that contain the unique identifier (such as the primary key), and the columns to be updated, as shown in the following example:

[C#] 
string updateSQL As String = "UPDATE emp SET sal = ?, job = ? + 
     = WHERE empno = ?; 

The parameterized query statements define the parameters that will be created. See "Parameter Markers" for more information about using parameters.

The following code example uses the Parameters.Add method to create the parameters for the preceding SQL statement, fill a DataSet, and print the updated table.

NOTE: The example requires the emp table (see "Sample Tables for Oracle"). The Oracle database in this example does not require the Database connection string option.

void test () { 
   string updateSQL = "UPDATE emp SET sal = ?, job = ? WHERE empno = ?"; 
   string selectText = "SELECT sal, job, empno FROM emp"; 
   string updateText = "UPDATE emp SET sal = ?, job = ? WHERE empno = ?"; 
   SequeLinkConnection con = new SequeLinkConnection("host=baleen; 
                                 User ID=test01;Password=test01"); 
   SequeLinkDataAdapter adapter = new SequeLinkDataAdapter(selectText, Conn); 
   SequeLinkCommand updateCommand = new SequeLinkCommand(updateText, Conn); 
   updateCommand.Parameters.Add("@sal", SequeLinkDbType.Decimal, 0, "SAL"); 
   updateCommand.Parameters.Add("@job", SequeLinkDbType.Varchar, 9, "JOB"); 
   updateCommand.Parameters.Add("@empno", SequeLinkDbType.Int, 0,
                                   "EMPNO"); 
   updateCommand.Parameters["@empno"].SourceVersion = DataRowVersion.Original; 
   adapter.UpdateCommand = updateCommand; 
   DataSet   myDataSet = new DataSet("emp"); 
   adapter.Fill(myDataSet, "emp"); 
   // print 
   PrintTable(myDataSet); 
   // Give employee number 11 a promotion and a raise 
   DataRow changeRow = myDataSet.Tables["emp"].Rows[10]; 
   changeRow["sal"] = "35000"; 
   changeRow["job"] = "MANAGER"; 
   // Send back to database and reprint 
   try 
   { 
      adapter.Update(myDataSet, "emp"); 
      myDataSet.Dispose(); 
      myDataSet = new DataSet(); 
      adapter.Fill(myDataSet, "emp"); 
      PrintTable(myDataSet); 
   } 
   catch (Exception ex)  
   { 
     // Display any exceptions in a messagebox 
     MessageBox.Show (ex.Message); 
   } 
   //  Close the connection 
   Conn.Close(); 
}